| Items Sold |
|
SELECT |
Details |
SELECT
DISTINCT "INVOICE"."ID" AS "INVOICE",
"INVOICE"."DATE",
"ITEMS"."ITEM",
"SOLD"."QTY",
"ITEMS"."SKU" AS "SERIAL",
"ITEMS"."PRICE",
"FIRSTNAME" || ' ' || "LASTNAME" AS "CUSTOMER"
FROM
"SOLD",
"ITEMS",
"INVOICE",
"CUSTOMERS"
WHERE
"SOLD"."ITEM_ID" = "ITEMS"."ITEM_ID"
AND "INVOICE"."ID" = "SOLD"."INVOICE_ID"
AND "CUSTOMERS"."ID" = "INVOICE"."CUSTOMER_ID"
AND "SOLD"."ITEM_ID" IS NOT NULL |
| Items UnSold |
|
SELECT |
Details |
SELECT
"ITEMS".*
FROM
{ OJ "ITEMS"
LEFT OUTER JOIN "SOLD" ON "ITEMS"."ITEM_ID" = "SOLD"."ITEM_ID" }
WHERE
"SOLD"."ITEM_ID" IS NULL |
| qSubForm_Adv_Discount |
qSubForm_Adv_Total (Query)
4. Invoice Advanced|SubForm_SubTotal|SubForm_Discount (Control) |
SELECT |
Details |
SELECT
"INVOICE"."ID" AS "INVOICE_ID",
- CASEWHEN(
"INVOICE"."DISCOUNT_ID" = 0,
0,
"SUBTOTAL" * (
ABS("UNITS" / 100))) AS "INVOICE_DISCOUNT"
FROM
"qSubForm_SubTotal",
"INVOICE",
"DISCOUNT"
WHERE
"qSubForm_SubTotal"."INVOICE_ID" = "INVOICE"."ID"
AND "INVOICE"."DISCOUNT_ID" = "DISCOUNT"."ID" |
| qSubForm_Adv_Shipping |
qSubForm_Adv_Total (Query)
4. Invoice Advanced|SubForm_SubTotal|SubForm_Shipping (Control) |
SELECT |
Details |
SELECT
"SOLD"."INVOICE_ID",
SUM(
CASEWHEN(
"ITEMS"."TAXABLE" = 1,
CASEWHEN(
"SHIPPERS"."ID" > 0,
COALESCE ("SHIPPERS"."BASE_FEE", 0) + (
(
COALESCE ("ITEMS"."PACKAGING_FEE", 0) + (
COALESCE ("ITEMS"."SHIPPING_WT", 0) * COALESCE ("SHIPPERS"."WEIGHT_RATE", 0))) * "SOLD"."QTY"),
0),
0)) AS "SHIPPING"
FROM
"ITEMS",
"SOLD",
"INVOICE",
"SHIPPERS"
WHERE
"ITEMS"."ITEM_ID" = "SOLD"."ITEM_ID"
AND "INVOICE"."ID" = "SOLD"."INVOICE_ID"
AND "SHIPPERS"."ID" = "INVOICE"."SHIPPER_ID"
GROUP BY
"SOLD"."INVOICE_ID" |
| qSubForm_Adv_Tax |
qSubForm_Adv_Total (Query)
4. Invoice Advanced|SubForm_SubTotal|SubForm_Tax (Control) |
SELECT |
Details |
SELECT
"SOLD"."INVOICE_ID",
SUM(
CASEWHEN(
"ITEMS"."TAXABLE" = 1,
(
"ITEMS"."PRICE" - ABS(
COALESCE ("SOLD"."DISC_CURRENCY", 0))) * (
1 - ABS(
COALESCE ("SOLD"."DISC_PERCENT", 0))) * "SOLD"."QTY" * "TAX"."RATE" * (
CASEWHEN(
"DISCOUNT"."ID" = 0,
1,
1 - ("DISCOUNT"."UNITS" / 100))),
0)) AS "TAXES"
FROM
"ITEMS",
"SOLD",
"INVOICE",
"TAX",
"DISCOUNT"
WHERE
"ITEMS"."ITEM_ID" = "SOLD"."ITEM_ID"
AND "INVOICE"."TAX_ID" = "TAX"."ID"
AND "INVOICE"."ID" = "SOLD"."INVOICE_ID"
AND "DISCOUNT"."ID" = "INVOICE"."DISCOUNT_ID"
GROUP BY
"SOLD"."INVOICE_ID" |
| qSubForm_Adv_Total |
4. Invoice Advanced|SubForm_SubTotal|SubForm_Total (Control) |
SELECT |
Details |
SELECT
"qSubForm_SubTotal"."INVOICE_ID",
COALESCE ("SUBTOTAL", 0) + COALESCE ("INVOICE_DISCOUNT", 0) + COALESCE ("TAXES", 0) + COALESCE ("SHIPPING", 0) AS "TOTAL"
FROM
"qSubForm_Adv_Tax",
"qSubForm_SubTotal",
"qSubForm_Adv_Shipping",
"qSubForm_Adv_Discount"
WHERE
"qSubForm_Adv_Tax"."INVOICE_ID" = "qSubForm_SubTotal"."INVOICE_ID"
AND "qSubForm_Adv_Shipping"."INVOICE_ID" = "qSubForm_SubTotal"."INVOICE_ID"
AND "qSubForm_Adv_Discount"."INVOICE_ID" = "qSubForm_SubTotal"."INVOICE_ID" |
| qSubForm_SubTotal |
qSubForm_Adv_Discount (Query)
qSubForm_Adv_Total (Query)
qSubForm_Total (Query)
3. Invoice Intermediate|SubForm_SubTotal (Control)
4. Invoice Advanced|SubForm_SubTotal (Control) |
SELECT |
Details |
SELECT
"SOLD"."INVOICE_ID",
SUM(
(
"ITEMS"."PRICE" - ABS(
COALESCE ("SOLD"."DISC_CURRENCY", 0))) * "SOLD"."QTY" * (
1 - ABS(
COALESCE ("SOLD"."DISC_PERCENT", 0)))) AS "SUBTOTAL"
FROM
"ITEMS",
"SOLD"
WHERE
"ITEMS"."ITEM_ID" = "SOLD"."ITEM_ID"
GROUP BY
"SOLD"."INVOICE_ID" |
| qSubForm_Tax |
qSubForm_Total (Query)
3. Invoice Intermediate|SubForm_SubTotal|SubForm_Tax (Control) |
SELECT |
Details |
SELECT
"SOLD"."INVOICE_ID",
SUM(
CASEWHEN(
"ITEMS"."TAXABLE" = 1,
(
"ITEMS"."PRICE" - ABS(
COALESCE ("SOLD"."DISC_CURRENCY", 0))) * (
1 - ABS(
COALESCE ("SOLD"."DISC_PERCENT", 0))) * "SOLD"."QTY" * "TAX"."RATE",
0)) AS "TAXES"
FROM
"ITEMS",
"SOLD",
"TAX"
WHERE
"ITEMS"."ITEM_ID" = "SOLD"."ITEM_ID"
AND "TAX"."ID" = 1
GROUP BY
"SOLD"."INVOICE_ID" |
| qSubForm_Total |
3. Invoice Intermediate|SubForm_SubTotal|SubForm_Total (Control) |
SELECT |
Details |
SELECT
"qSubForm_SubTotal"."INVOICE_ID",
"SUBTOTAL" + "TAXES" AS "TOTAL"
FROM
"qSubForm_Tax",
"qSubForm_SubTotal"
WHERE
"qSubForm_Tax"."INVOICE_ID" = "qSubForm_SubTotal"."INVOICE_ID" |
